USE BishoyPhoto
GO

ALTER TABLE [dbo].[Photo] DROP [DF_Photo_LocationLarge]
GO

ALTER TABLE [dbo].[Photo] DROP COLUMN [LocationLarge]
GO

ALTER TABLE [dbo].[Photo] ADD  CONSTRAINT[IX_Photo_Location] UNIQUE ([Location])
GO

ALTER PROC [dbo].[AddPhoto] (@Title AS nvarchar(200)
           ,@Description AS nvarchar(500)
           ,@Location AS nvarchar(512)
           ,@GalleryID AS int
           ,@SlideShow AS bit)
AS

DECLARE @NextOrder as int
DECLARE @NextSlideShowOrder as int

SET @NextOrder = ISNULL((SELECT MAX([Order]) FROM [dbo].[Photo] WHERE [GalleryID] = @GalleryID), 0) + 1
SET @NextSlideShowOrder = ISNULL((SELECT MAX([SlideShowOrder]) FROM [dbo].[Photo]), 0) + 1

INSERT INTO [dbo].[Photo]
           ([Title]
           ,[Description]
           ,[Order]
           ,[Location]
           ,[GalleryID]
           ,[SlideShow]
           ,[SlideShowOrder])
     VALUES
           (@Title
           ,@Description
           ,@NextOrder
           ,@Location
           ,@GalleryID
           ,@SlideShow
           ,CASE @SlideShow WHEN 0 THEN NULL ELSE @NextSlideShowOrder END)
GO

ALTER PROC [dbo].[UpdatePhoto] (@ID AS int
      ,@Title AS nvarchar(200)
      ,@Description AS nvarchar(500)
      ,@Order AS int
      ,@Location AS nvarchar(512)
      ,@GalleryID AS int
      ,@SlideShow AS bit
      ,@SlideShowOrder AS int)
AS

IF @Title IS NOT NULL BEGIN UPDATE [dbo].[Photo] SET [Title] = @Title WHERE ID = @ID END
IF @Description IS NOT NULL BEGIN UPDATE [dbo].[Photo] SET [Description] = @Description WHERE ID = @ID END
IF @Location IS NOT NULL BEGIN UPDATE [dbo].[Photo] SET [Location] = @Location WHERE ID = @ID END
IF @Order IS NOT NULL BEGIN UPDATE [dbo].[Photo] SET [Order] = @Order WHERE ID = @ID END
IF @SlideShow IS NOT NULL BEGIN UPDATE [dbo].[Photo] SET [SlideShow] = @SlideShow WHERE ID = @ID END
IF @SlideShowOrder IS NOT NULL BEGIN UPDATE [dbo].[Photo] SET [SlideShowOrder] = @SlideShowOrder WHERE ID = @ID END
GO

ALTER PROC [dbo].[GetGalleryPhotos] (@GalleryID AS int)
AS
SELECT [ID]
      ,[Title]
      ,[Description]
      ,[Location]
  FROM [dbo].[Photo]
  WHERE [GalleryID] = @GalleryID
  ORDER BY [Order]
GO

ALTER PROC [dbo].[GetSlideShowPhotos]
AS
SELECT [ID]
      ,[Title]
      ,[Description]
      ,[Location]
  FROM [dbo].[Photo]
  WHERE [SlideShow] = 1
  ORDER BY [SlideShowOrder]

GO

ALTER PROC [dbo].[UpdateGallery] (@ID int, @Name AS nvarchar(200), @Order AS int, @Password AS nvarchar(8), @Description AS nvarchar(500), @ArchiveURL AS varchar(256))
AS

IF @Name IS NOT NULL BEGIN UPDATE [dbo].[Gallery] SET [Name] = @Name WHERE ID = @ID END
IF @Order IS NOT NULL BEGIN UPDATE [dbo].[Gallery] SET [Order] = @Order WHERE ID = @ID END
IF @Password IS NOT NULL BEGIN UPDATE [dbo].[Gallery] SET [Password] = @Password WHERE ID = @ID END
IF @Description IS NOT NULL BEGIN UPDATE [dbo].[Gallery] SET [Description] = @Description WHERE ID = @ID END
IF @ArchiveURL IS NOT NULL BEGIN UPDATE [dbo].[Gallery] SET [ArchiveURL] = @ArchiveURL WHERE ID = @ID END
GO

CREATE TABLE [dbo].[Admin](
	[ID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [Admin_PrimaryKey] PRIMARY KEY,
	[FullName] [nvarchar](200) NOT NULL,
	[Username] [nvarchar](20) NOT NULL CONSTRAINT [Admin_UniqueKey] UNIQUE,
	[Password] [nvarchar](20) NOT NULL,
	[Email] [nvarchar](128) NOT NULL
)
GO

INSERT INTO [dbo].[Admin]([FullName], [Username], [Password], [Email]) VALUES(N'Bishoy Botros', N'bishoy', N'Virgin3', N'photography@bishoy.com.au')
INSERT INTO [dbo].[Admin]([FullName], [Username], [Password], [Email]) VALUES(N'Margret Botros', N'margret', N'Mariam', N'margret@bishoyphotography.com')
GO

CREATE PROC [dbo].[GetAdmin] (@Username AS nvarchar(20), @Password AS nvarchar(20))
AS
	SELECT [ID], [FullName], [Email] FROM [Admin] WHERE [Username] = @Username AND [Password] = @Password
GO
